package cn.wp.ewallet.wmps.dao.impl;

import cn.hutool.core.util.StrUtil;
import cn.wp.common.core.dao.JdbcSupportTemplate;
import cn.wp.common.core.dao.SqlString;
import cn.wp.common.core.model.WpPage;
import cn.wp.ewallet.wmps.dao.FinaProdInfoDao;
import cn.wp.ewallet.wmps.extension.constant.EwalletWmpsConstant;
import cn.wp.ewallet.wmps.model.dto.FinaParmDto;
import cn.wp.ewallet.wmps.model.entity.*;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by yangjin on 2020/1/7 12:37
 */
@Repository
public class FinaProdInfoDaoImpl implements FinaProdInfoDao {

    @Autowired
    private JdbcSupportTemplate jdbcSupportTemplate;

    @Override
    public WpPage<FinaProdInfoDoo> selectAllFinaProdInfoList(String finaProdName, WpPage page) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select t.fina_prod_id, t.fina_prod_name,t.fina_prod_desc,t.prod_type,t.min_purch_amount,t.yield_rate,t.return_rate_cycle,");
        sqlString.add("t.status, t.status_date,s1.sys_user_code as create_user,t.create_date,s2.sys_user_code as update_user,t.update_date,");
        sqlString.add("t.fina_prod_cycle, t.is_early_redemp, t.free_period, t.discount_rate ");
        sqlString.add("from ( select f.*, x.fina_prod_cycle, x.is_early_redemp, x.free_period, x.discount_rate ");
        sqlString.add("from fina_prod_info f, fix_fina_prod_info x where f.fina_prod_id = x.fina_prod_id ) t ");
        sqlString.add("left join system_user_info s1 on t.create_user = s1.sys_user_id");
        sqlString.add("left join system_user_info s2 on t.update_user = s2.sys_user_id");
        sqlString.add("where t.prod_type = ?");
        List<String> params = new ArrayList<>();
        params.add( EwalletWmpsConstant.PROD_TYPE_REGULAR);
        if (StringUtils.isNotEmpty(finaProdName)) {
            sqlString.add("and t.fina_prod_name like ?");
            params.add("%"+finaProdName+"%");
        }
        return jdbcSupportTemplate.selectList(sqlString.build(), params.toArray(), page, FinaProdInfoDoo.class);

    }

    @Override
    public WpPage<FinaProdInfoDoo> selectUpperFindProdInfoList(WpPage page) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select f.FINA_PROD_ID, f.FINA_PROD_NAME, f.FINA_PROD_DESC, f.MIN_PURCH_AMOUNT, f.YIELD_RATE, f.RETURN_RATE_CYCLE,");
        sqlString.add(" x.FINA_PROD_CYCLE, x.IS_EARLY_REDEMP, x.FREE_PERIOD, x.DISCOUNT_RATE");
        sqlString.add("from `fina_prod_info` f, fix_fina_prod_info x");
        sqlString.add("where f.FINA_PROD_ID = x.FINA_PROD_ID");
        sqlString.add("and f.`STATUS` = '1000' and f.`PROD_TYPE` = '1000'");
        return jdbcSupportTemplate.selectList(sqlString.build(),null,page,FinaProdInfoDoo.class);
    }

    @Override
    public int insertFinaProdInfo(FinaProdInfoDo finaProdInfoDo) {
        return jdbcSupportTemplate.insert(finaProdInfoDo);
    }

    @Override
    public FinaProdInfoDo selectFinaProdInfoById(String finaProdId) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select f.* from fina_prod_info f where f.status = ? and f.fina_prod_id = ?");
        return jdbcSupportTemplate.selectObject(sqlString.build(), new Object[]{
                EwalletWmpsConstant.FINA_PROD_STATUS_UPPER,
                finaProdId
        }, FinaProdInfoDo.class);
    }

    @Override
    public int updateFindProdInfoById(String finaProdId) {
        return jdbcSupportTemplate.execute("update fina_prod_info set `STATUS` = ? where status != ? and FINA_PROD_ID = ?", new Object[]{
                EwalletWmpsConstant.FINA_PROD_STATUS_UPPER,
                EwalletWmpsConstant.FINA_PROD_STATUS_UPPER,
                finaProdId
        });
    }

    @Override
    public int updateFindProdInfoById2(String finaProdId) {
        return jdbcSupportTemplate.execute("update fina_prod_info set `STATUS` = ? where status = ? and FINA_PROD_ID = ?", new Object[]{
                EwalletWmpsConstant.FINA_PROD_STATUS_LOWER,
                EwalletWmpsConstant.FINA_PROD_STATUS_UPPER,
                finaProdId
        });
    }

    @Override
    public List<PurchasedProdOrderRelDoo> selectFixfinaProdInfo(String accountId, String status1, String status2) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select *");
        sqlString.add("from FINA_PROD_INFO a, PROD_ORDER_REL b, FIX_PROD_ORDER_REL c,FIX_FINA_PROD_INFO d");
        sqlString.add("where a.FINA_PROD_ID = b.FINA_PROD_ID");
        sqlString.add("    and b.ACCOUNT_ID = ?");
        sqlString.add("    and (b.ORDER_STATUS = ? or b.ORDER_STATUS = ?)");
        sqlString.add("    and a.PROD_TYPE = ?");
        sqlString.add("    and b.PROD_ORDER_ID = c.PROD_ORDER_ID");
        sqlString.add("    and a.FINA_PROD_ID = d.FINA_PROD_ID");
        return jdbcSupportTemplate.selectList(sqlString.build(), new Object[]{accountId, status1, status2, EwalletWmpsConstant.PROD_TYPE_REGULAR}, PurchasedProdOrderRelDoo.class);
    }

    @Override
    public BigDecimal selectSumAlreadyReturnYeld(String accountId, String returnStatus) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select sum(c.RETURN_YIELD)");
        sqlString.add("from PROD_ORDER_REL a, FINA_PROD_INFO b, RETURN_YIELD_INFO c");
        sqlString.add("where a.FINA_PROD_ID = b.FINA_PROD_ID");
        sqlString.add("    and b.PROD_TYPE = ?");
        sqlString.add("    and a.PROD_ORDER_ID = c.PROD_ORDER_ID");
        sqlString.add("    and c.RETURN_STATUS = ?");
        sqlString.add("    and a.ACCOUNT_ID = ?");
        return jdbcSupportTemplate.selectValue(sqlString.build(), new Object[]{EwalletWmpsConstant.PROD_TYPE_REGULAR, returnStatus, accountId}, BigDecimal.class);
    }

    @Override
    public BigDecimal selectSumPurchasedYeld(String accountId) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select sum(a.PURCH_AMOUNT)");
        sqlString.add("from PROD_ORDER_REL a, FINA_PROD_INFO b");
        sqlString.add("where a.FINA_PROD_ID = b.FINA_PROD_ID");
        sqlString.add("    and b.PROD_TYPE = ?");
        sqlString.add("    and a.ACCOUNT_ID = ?");
        return jdbcSupportTemplate.selectValue(sqlString.build(), new Object[]{EwalletWmpsConstant.PROD_TYPE_REGULAR, accountId}, BigDecimal.class);
    }

    /**
     * 通过理财类型 查看理财产品id
     * @param proType
     * @return
     */
    @Override
    public List<FinaProdInfoDo> selectFinaProdInfoByProcType(String proType) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("SELECT * from fina_prod_info");
        sqlString.add("WHERE PROD_TYPE= ?");
        return jdbcSupportTemplate.selectList(sqlString.build(), new Object[]{proType}, FinaProdInfoDo.class);
    }

    @Override
    public void updateFindProdInfo(FinaProdInfoDo finaProdInfoDo) {
        jdbcSupportTemplate.updateSelective(finaProdInfoDo);
    }

    @Override
    public FinaProdInfoDo selectTtyParams() {
        String sql = "select * from fina_prod_info where prod_type = '2000'";
        return jdbcSupportTemplate.selectObject(sql,null,FinaProdInfoDo.class);
    }

    @Override
    public WpPage<FinaProdCountDoo> getFinaProdCountDoos(String finaProdIdTty,WpPage wpPage) {
        SqlString sql =new SqlString();
        sql.add("select b.FINA_PROD_ID,b.FINA_PROD_NAME,count(DISTINCT ACCOUNT_ID) fina_prod_count_user,sum(PURCH_AMOUNT) fina_prod_count_money  ");
        sql.add("from fina_prod_info b left join PROD_ORDER_REL a on");
        sql.add(" a.FINA_PROD_ID=b.FINA_PROD_ID   ");
//        只查询天天盈 产品的 概况信息
        if (wpPage==null){
            sql.add(" where b.FINA_PROD_ID = ?  ");
            return jdbcSupportTemplate.selectList(sql.build(),new Object[]{finaProdIdTty},new WpPage(1,1), FinaProdCountDoo.class);
        }else{
            sql.add(" where b.FINA_PROD_ID!= ? group  BY b.FINA_PROD_ID");
            return jdbcSupportTemplate.selectList(sql.build(),new Object[]{finaProdIdTty},wpPage, FinaProdCountDoo.class);
        }
    }

    @Override
    public WpPage<FinaProdAddressDoo> selectFinaProdInfoListByParam(FinaParmDto finaParmDto, WpPage wpPage) {
        SqlString sqlString = SqlString.newInstance();
        sqlString.add("select a.fina_prod_id, a.FINA_PROD_NAME, a.PROD_TYPE, a.YIELD_RATE,b.PROD_ORDER_ID, b.PURCH_AMOUNT");
        sqlString.add("    , b.PURCH_TIME, b.EFF_DATE, b.ORDER_STATUS, c.WALLET_ADDRESS, e.FINA_PROD_CYCLE,f.EXP_TIME,f.actual_yield ");
        sqlString.add("from fina_prod_info a, PROD_ORDER_REL b, ACCOUNT_INFO c, FIX_FINA_PROD_INFO e ,FIX_PROD_ORDER_REL f");
        sqlString.add("where a.FINA_PROD_ID = b.FINA_PROD_ID");
        sqlString.add("    and b.ACCOUNT_ID = c.ACCOUNT_ID");
        sqlString.add("    and a.FINA_PROD_ID = e.FINA_PROD_ID");
        sqlString.add("   and f.PROD_ORDER_ID=b.PROD_ORDER_ID");
        List list=new ArrayList();
        if(!StrUtil.isBlank(finaParmDto.getBeginTime())){
            sqlString.add("  and b.PURCH_TIME >= ?");
            list.add(finaParmDto.getBeginTime());
        }
        if(!StrUtil.isBlank(finaParmDto.getEndTime())){
            sqlString.add("  and b.PURCH_TIME <= ?");
            list.add(finaParmDto.getEndTime());

        }
        if(!StrUtil.isBlank(finaParmDto.getFinaProdName())){
            sqlString.add(" and a.FINA_PROD_NAME like ?");
            list.add("%"+finaParmDto.getFinaProdName()+"%");
        }
        sqlString.add("  order by b.PURCH_TIME desc");
        return jdbcSupportTemplate.selectList(sqlString.build(),list.toArray(),wpPage,FinaProdAddressDoo.class);


    }


    /**
     * 天天盈意外的 所有理财产品
     * @param finaProdIdTty
     * @return
     */
    @Override
    public List<FinaProdCountDoo> getFinaProdCountDooByLicai(String finaProdIdTty ) {
        SqlString sql =new SqlString();
        sql.add("select b.FINA_PROD_ID,b.FINA_PROD_NAME,count(DISTINCT ACCOUNT_ID) fina_prod_count_user,sum(PURCH_AMOUNT) fina_prod_count_money  ");
        sql.add("from fina_prod_info b left join PROD_ORDER_REL a on");
        sql.add(" a.FINA_PROD_ID=b.FINA_PROD_ID  and  a.ORDER_STATUS = ? ");
        sql.add(" where b.FINA_PROD_ID!= ? group  BY b.FINA_PROD_ID");
        return jdbcSupportTemplate.selectList(sql.build(),new Object[]{EwalletWmpsConstant.ORDER_STATUS_BEARING,finaProdIdTty}, FinaProdCountDoo.class);
    }
}
